﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Purchases
{
    public class PurchaseOrderManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOR WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Add/Update Order
        public void AddPurchaseOrder(PurchaseOrderUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OPOR (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, CardCode, CardName, ContactPerson, Reference, BillTo, ShipTo, Remarks, RequestedBy, Division, Department, EntityCode, WithHoldingTax, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @CardCode, @CardName, @ContactPerson, @Reference, @BillTo, @ShipTo, @Remarks, @RequestedBy, @Division, @Department, @EntityCode, @WithHoldingTax, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@EntityCode", oUnit.EntityCode));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));

            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO POR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";



                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                if (oRow["IsFree"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                }
                else
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                }
                oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                oCommand.ExecuteNonQuery();

            }
        }

        public void UpdatePurchaseOrder(PurchaseOrderUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, Remarks=@Remarks, RequestedBy=@RequestedBy, Division=@Division, Department=@Department, EntityCode=@EntityCode, GrossAmount=@GrossAmount, VATAmount=@VATAmount, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, WithHoldingTax=@WithHoldingTax, DateModified=@DateModified, ModifiedBy=@ModifiedBy" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@EntityCode", oUnit.EntityCode));
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));

            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));

            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE POR1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO POR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";


                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    if (oRow["Qty"] == DBNull.Value)
                    {
                        int _ZeroValue = 0;
                        oRow["Qty"] = _ZeroValue;
                    }
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    oCommand.ExecuteNonQuery();

                }

            }
        }

        public void SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT));

            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Post/Cancel/Close Order
        public void PostPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void CancelPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void ChangePrintStatus(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET DOCSTATUS='Printed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void ClosePO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOR SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Data

        public DataTable GetPostedPO(string _REFERENCE_PO)
        {
            SqlCommand oCommand = new SqlCommand();
            _REFERENCE_PO = '%' + _REFERENCE_PO.Trim() + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_POSTEDORDER where DOCSTATUS='Posted' AND DOCNUM LIKE @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _REFERENCE_PO));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedPO()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_POSTEDORDER where DOCSTATUS='Posted'";

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedPOByDocNum(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_POSTEDORDER WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DocumentNumber));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseOrderBySupplier(string _CardName)
        {
            _CardName = '%' + _CardName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPOR WHERE CardName LIKE @CardName ORDER BY POSTINGDATE DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetPurchaseOrders(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPOR WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseOrder(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            _DocNumber = '%' + _DocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OPOR WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public string GetPurchaseOrderNumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocNum FROM OPOR WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count == 0)
            {
                return "";
            }
            else
            {
                DataRow oRow = ds.Tables[0].Rows[0];
                return oRow["DocNum"].ToString();
            }

        }

        public DataRow GetPurchaseOrder(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOR WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetPOLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, 'N/A' LotNumber, CAST(GetDate() AS DateTime) MfrDate,  CAST(GetDate() AS DateTime) ExpiryDate FROM POR1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Report Retreivable
        
        public DataTable GetPOHeadersByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft,  bool _Posted, bool _IsPrinted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;

            oCommand.CommandText = "SP_LOGS_PO";
            oCommand.Parameters.Add(new SqlParameter("@FROM",           _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO",             _TO));
            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT",      Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED",     Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPRINTED",    Convert.ToInt16(_IsPrinted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN",       Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED",     Convert.ToInt16(_Closed)));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetPurchaseOrderStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_PO_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetLastPurchasePrice(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LAST_PUR_PRICE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
            

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
